package com.tim.util;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.map.MapUtil;
import com.alibaba.fastjson.JSON;

import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * 获取连接，执行sql
 * @author litianlin
 * @date   2019年6月28日上午9:54:45
 * @Description TODO
 */
public class DbUtils {
  public static void main(String[] args) throws Exception {
	  Connection connection = getConnection(
	  		"jdbc:mysql://rm-bp16tdzf48mvnql762o.mysql.rds.aliyuncs.com:3306/xqxc_admin",
			  "dev_admin", "Dev+2019@xqxc");

	  Statement stmt = connection.createStatement();
	  String[] strs = "admin_com_sms_log,admin_param_info,agreement_info,xqxc_goods.audit_info".split(",");
	  for(String str : strs){
		  String query = String.format("select * from %s order by id desc limit 100;", str);
		  System.out.println(query);
		  ResultSet resultSet = stmt.executeQuery(query);
		  String insertSql = rsToInsert(resultSet);
      Files.write(Paths.get("C:\\Users\\Administrator\\Desktop/ttt/"+str+".sql"), Arrays.asList(insertSql));
//		  System.out.println(insertSql);
	  }
  }

	public static String rsToInsert(ResultSet rs) throws SQLException {
		StringBuilder sb = new StringBuilder();
		ResultSetMetaData metaData = rs.getMetaData();
		String tableName = metaData.getTableName(1);
		sb.append("INSERT INTO ").append(tableName);
		List<String> columns = getColumns(rs);
		String columnInfo = columns.stream().collect(Collectors.joining(","));

		List<String> values = getValues(rs);
		String valueInfo = values.stream()
				.map(e->"("+e+")")
				.collect(Collectors.joining(",\n"));
		sb.append(" ( ").append(columnInfo).append(" ) VALUES \n").append(valueInfo).append(" ;\n");

		return sb.toString();
	}

	public static List<String> getColumns(ResultSet rs) throws SQLException {
		List<String> list = new ArrayList<>();
		ResultSetMetaData metaData = rs.getMetaData();
		int columnCount = metaData.getColumnCount();
		for(int i=1;i<=columnCount;i++){
			list.add(metaData.getColumnName(i));
		}

		return list;
	}

	public static List<String> getValues(ResultSet rs) throws SQLException {
		List<String> list = new ArrayList<>();
		ResultSetMetaData metaData = rs.getMetaData();
		int columnCount = metaData.getColumnCount();
		while(rs.next()){
			StringBuilder builder = new StringBuilder();
			for(int i=1;i<=columnCount;i++){
				builder.append(getColumnValue(metaData.getColumnType(i), i, rs))
					.append(", ");
			}
			list.add(builder.substring(0, builder.length()-2));
		}

		return list;
	}

	public static String getColumnValue(int columnType, int i, ResultSet rs) throws SQLException {
		switch(columnType){
			case Types.NUMERIC :return rs.getLong(i) + "";
			case Types.VARCHAR:
				return (rs.getString(i) == null) ? null : "'"+rs.getString(i)+"'";
			case Types.DATE:
				return (rs.getDate(i) == null) ? null:"'"+ DateUtil.format(rs.getDate(i), "yyyy-MM-dd")+"'";
			case Types.TIMESTAMP:
				return (rs.getTimestamp(i) == null) ? null:"'"+DateUtil.format(rs.getTimestamp(i),"yyyy-MM-dd HH:mm:ss")+"'";
			case Types.TIME:
				return (rs.getTime(i) == null) ? null:"'"+DateUtil.format(rs.getTime(i),"yyyy-MM-dd HH:mm:ss")+"'";
			case Types.BOOLEAN:return rs.getInt(i) + "";
			case Types.ARRAY :
				return JSON.toJSONString(rs.getArray(i));
			case Types.BIGINT :return rs.getBigDecimal(i) + "";
			case Types.BINARY:
				return IOPrintUtil.inputStreamToStr(rs.getBinaryStream(i));
			case Types.BLOB:
				return IOPrintUtil.inputStreamToStr(rs.getBlob(i).getBinaryStream());
			case Types.CHAR:
				return (rs.getString(i) == null) ? null : "'"+rs.getString(i)+"'";
			case Types.INTEGER:return rs.getInt(i) + "";
			case Types.DOUBLE :return rs.getDouble(i) + "";
			case Types.FLOAT:return rs.getFloat(i) + "";
			case Types.SMALLINT:return rs.getInt(i) + "";
			case Types.TINYINT:return rs.getInt(i) + "";
			case Types.DECIMAL:return rs.getLong(i) + "";
			default:return null;
		}
	}

	public static final Map<String,String> DRIVERS = MapUtil.builder("jdbc:oracle","oracle.jdbc.driver.OracleDriver")
			.put("jdbc:mysql","com.mysql.cj.jdbc.Driver")
			.put("jdbc:h2", "org.h2.Driver")
			.build();
	public static Connection getConnection(String driverName,String url,String uname,String pwd) throws Exception {
		if(driverName==null) {
			throw new RuntimeException("Drivername is null");
		}
		Class.forName(driverName);
		return DriverManager.getConnection(url,uname,pwd);
	}
	public static Connection getConnection(String url,String uname,String pwd) throws Exception {
		String temp = url.toLowerCase().trim();
		temp=temp.substring(0, temp.indexOf(":", temp.indexOf(":")+1));
		String driver = DRIVERS.get(temp);
		if(driver==null) {
			throw new RuntimeException("UnSupported url");
		}
		return getConnection(driver,url,uname,pwd);
	}
	public static void executeNonQuery(Connection conn,String sql) throws SQLException, Exception {
		if(Objects.requireNonNull(sql, "sql can't be null").toLowerCase().trim().startsWith("select")) {
			throw new RuntimeException("Can't be a query sql.");
		}
		try(
				Statement stmt = conn.createStatement();
			){
			 stmt.execute(sql);		
			}
		
	}
	public static void executeQuery(Connection conn,String sql,Function<ResultSet,Void> callback) throws SQLException, Exception {
		if(!Objects.requireNonNull(sql, "sql can't be null").toLowerCase().trim().startsWith("select")) {
			throw new RuntimeException("Must be a query sql.");
		}
		try(
				Statement stmt = conn.createStatement();
			){
			 ResultSet rs = stmt.executeQuery(sql);
			 if(callback!=null) {
				 callback.apply(rs);
			 }
			}
	}
}
